package jfarp

import cn.hutool.core.io.resource.ClassPathResource
import com.ax.framework.jfinal.db.*
import com.ax.framework.jfinal.engine.PDirective
import com.jfinal.plugin.activerecord.Db
import com.jfinal.plugin.activerecord.DbPro
import com.jfinal.template.source.ClassPathSourceFactory
import jfarp.model.Product
import org.intellij.lang.annotations.Language

/**
 * 注意事项
 *
 *@author  小杨
 *@datetime  2018/11/22 0022 16:42
 *
 * */
fun main(args: Array<String>) {
    //加载yml文件
    val stream = ClassPathResource("datasource.yml").stream
    //初始化数据源
    Arp.init(stream)

    //
    val count = Db.use().queryLong("select 1")
    println(count)

    val dp = Db.use()
    val sql_kit = dp.config.sqlKit

    sql_kit.engine
            .removeDirective("p")
            .addDirective("p", PDirective::class.java)
            .setToClassPathSourceFactory() //sql_kit将从classpath中获取文件
    //添加一个汇总作用的模板
    sql_kit.addSqlTemplate("sql/all.sql")
    sql_kit.parseSqlTemplate()

    val map = hashMapOf("id" to "123"
            , "name" to "云彩"
            , "price" to "721")

    usedb1(dp, map)
    usedb2(dp, map)
    usedb3(dp, map)

    usemodel1(map)
    usemodel2(map)
    usemodel3(map)

}


// ### db, 基本用法
fun usedb1(db: DbPro, map: Map<String, Any?>) {

    //  根根主键查询,
    //  Db.findById("other_table","other_table_pk_name","id_value")
    val record_a = Db.findById("jf_product", "id", "1")


    // 返回单个值
    //    Db.queryStr()
    //    Db.queryBigDecimal()
    //    Db.queryFloat()
    //    Db.queryDouble()
    //    Db.queryInt()
    val long = Db.queryLong("select count(1) from jf_product where id = ?", "1")

    /*
    * 返回集合
    * */
    val list_a = Db.find("select * from jf_product")
    val list_a2 = Db.find("select * from jf_product where 1=? or 2=? or 3=?", 1, 2, 3)

    // 分页
    val paginate_a = Db.paginate(1, 15, "select *", "from jf_product")
    val list = paginate_a.list
    val total_page = paginate_a.totalPage

}

// ### 从字符串模板解析sql
fun usedb2(db: DbPro, map: Map<String, Any?>) {

    //返回一个
    db.findOneByStrTpl("select count(1) from jf_product where id = 1::text")

    //返回集合
    @Language("sql")
    val sql_tpl = """
        select * from  jf_product  where 1 = 1
        #p(name,price)
        and product_name = ?
        or price = ?::decimal
        #end
        offset 0 limit 10
    """.trimIndent()
    Db.use().findListByStrTpl(sql_tpl)

    //返回单个值
    @Language("sql")
    val sql_tpl_3 = """
        select product_name from jf_product where 1 = 1
        #p(id)
        and id = ?
        #end
    """.trimIndent()
    db.findByStrTpl<String>(sql_tpl_3, map)
}


// ### 从sql文件中获取
fun usedb3(db: DbPro, map: Map<String, Any?>) {
    //返回一个
    db.findOneBySqlId("product.detail", hashMapOf("id" to "123"))

    //返回集合
    db.findListBySqlId("product.list", hashMapOf("id" to "123"))

    //返回单个值
    db.findBySqlId<String>("product.count")

    //分页查询
    val page = 1
    val limit = 15
    db.paginateBySqlId("product.list", page, limit, map)
}


//  基本用法
fun usemodel1(map: Map<String, Any?>) {
    //
    val dao = Product.dao

    //根据id查询
    dao.findById("1")

    //返回集合
    dao.find("select * from jf_product where 1=? and 2=? and 3=?", 1, 2, 3)

    //分页
    dao.paginate(1, 15, "select *", "from jf_product where 1=? and 2=? and 3=?", 1, 2, 3)

    //更新
    Product().update()

    //移除null值字段
    Product().removeNullValueAttrs().update()

    //删除
    Product().setId("1").delete()

    //更新 和 删除, 都需要主键
}

// 解析sql字符串模板
fun usemodel2(map: Map<String, Any?>) {
    //
    val dao = Product.dao
    //
    @Language("sql")
    val sql_tpl = """
        select * from  jf_product  where 1 = 1
        #p(name,price)
        and product_name = ?
        or price = ?::decimal
        #end
        offset 0 limit 10
    """.trimIndent()

    //返回集合
    dao.findListByStrTpl(sql_tpl)

    //返回一个
    dao.findOneByStrTpl(sql_tpl)

    //分页
    dao.paginateByStrTpl(sql_tpl, 1, 15, map)

}

// 从sql文件获取
fun usemodel3(map: Map<String, Any?>) {
    //
    val dao = Product.dao

    //查询一个
    val a = dao.findOneBySqlId("product.detail", map)

    //查询集合
    val b = dao.findListBySqlId("product.list", map)

    //分页查询
    val paginate_c = dao.paginateBySqlId("product.list", 1, 15, map)
}


